const mysql = require("mysql")
const db = mysql.createPool({
  host: "localhost",
  user: "root",
  password: "123456",
  database: "shop"
})
// 测试链接
db.query('select 1', (err, results) => {
  if (err) {
    return console.log(err.message)
  } else {
    //console.log(results) // [ RowDataPacket { '1': 1 } ]
  }
})
// 执行查询语句
db.query('select * from shop_admin', (err, results) => {
  if (err) {
    return console.log(err.message)
  } else {
    //console.log(results) // [ RowDataPacket { total: 36 } ]
  }
})
// 执行插入语句
const user = {account:33,password:12345,name:12,status:0,create_time:11111}
const sqlStr = 'insert into shop_admin values(?,?,?,?,?)'
db.query(sqlStr, [user.account,user.password,user.name,user.status,user.create_time], (err, results) => {
  if (err) {
    //return console.log(err.message)
  } else if (results.affectedRows === 1){
    console.log("插入数据成功")
  }
})
// 执行插入语句 便捷 user1和数据库字段对应
const user1 = {account:34,password:12345,name:12,status:0,create_time:11111}
const sqlStr1 = 'insert into shop_admin set ?'
db.query(sqlStr1, user1, (err, results) => {
  if (err) {
    return console.log(err.message)
  } else if (results.affectedRows === 1){
    console.log("插入数据成功")
  }
})
// 更新语句
const user2 = {account:123122, password:654222321}
const sqlStr2 = 'update shop_admin set account=?, password=? where name="12"'
db.query(sqlStr2, [user2.account, user2.password], (err, results) => {
  if (err) {
    return console.log(err.message)
  } else if (results.affectedRows > 0){
    console.log("更新数据成功")
  }
})
// 更新语句 便捷 user3和数据库字段对应
const user3 = {account:3333, password:3333}
const sqlStr3 = 'update shop_admin set ? where name="12"'
db.query(sqlStr3, user3, (err, results) => {
  if (err) {
    return console.log(err.message)
  } else if (results.affectedRows > 0){
    console.log("更新数据成功")
  }
})
// 删除数据
const sqlStr4 = 'delete from shop_admin where id=?'
db.query(sqlStr4, 166, (err, results) => {
  if (err) {
    console.log(err.message)
  } else if(results.affectedRows > 0) {
    console.log('删除成功')
  }
})
